/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package EntityDB;

import com.sun.istack.internal.logging.Logger;
import entity.Employee;
import java.lang.invoke.CallSite;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;

/**
 *
 * @author NgocNam
 */
public class employeeDB {
    private String URL = "jdbc:sqlserver://localhost;databaseName=LeaveManagerSystem";
    private String Use = "sa"; 
    private String Pass = "123456";
    
    private Connection conn;
        public employeeDB(){
        try {
            conn=DriverManager.getConnection(URL,Use,Pass);
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(employeeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
        public boolean addEmployee(Employee e){
        try {
            CallableStatement csmt=conn.prepareCall("{call addEmployee(?,?,?,?,?,?,?,?)}");
            csmt.registerOutParameter(1, Types.INTEGER);
            csmt.setString(2, e.getEmployeeName());
            csmt.setString(3, e.getDepartmentID());
            csmt.setInt(4, e.getAuthorID());
            csmt.setBoolean(5, e.getGender());
            csmt.setString(6, e.getAddress());
            csmt.setInt(7, e.getNumberPhone());
            csmt.setBoolean(8, e.getStatus());
                csmt.executeUpdate();
            int employeeID=csmt.getInt(1);
            csmt.setInt(1, employeeID);
            return true;
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(employeeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
        }
        
        public boolean updateEmployee(Employee e){
        try {
            CallableStatement csmt=conn.prepareCall("{call updateEmployee(?,?,?,?,?,?,?,?)}");
            csmt.setInt(1, e.getEmployeeID());
            csmt.setString(2, e.getEmployeeName());
            csmt.setString(3, e.getDepartmentID());
            csmt.setInt(4, e.getAuthorID());
            csmt.setBoolean(5, e.getGender());
            csmt.setString(6,e.getAddress());
            csmt.setInt(7, e.getNumberPhone());
            csmt.setBoolean(8, e.getStatus());
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
            
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(employeeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        public boolean removeEmployee(int employeeID){
        try {
            CallableStatement csmt=conn.prepareCall("{call removeEmployee(?)}");
            csmt.setInt(1, employeeID);
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(employeeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        public ArrayList<Employee> getEmployee(){
            ArrayList<Employee> list=new ArrayList<Employee>();
        try {
            CallableStatement csmt=conn.prepareCall("{call getEmployee()}");
            ResultSet rs=csmt.executeQuery();
            while(rs.next()){
                int employeeID=rs.getInt(1);
                String emloyeeName=rs.getString(2);
                String departmentID=rs.getString(3);
                int authouID=rs.getInt(4);
                boolean gender=rs.getBoolean(5);
                String address=rs.getString(6);
                int numberPhone=rs.getInt(7);
                boolean status=rs.getBoolean(8);
                   Employee e=new Employee(employeeID, emloyeeName, departmentID, authouID, gender, address, numberPhone, status);
                   list.add(e);
            }
        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(employeeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return list;
        }
}
